package com.dao.impl;

import com.bean.Courier;
import com.bean.User;
import com.dao.BaseCourierDao;
import com.exception.DuplicateUserPhoneException;
import com.util.DBUtils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CourierDaoMysql extends DBUtils implements BaseCourierDao {
    private static final String SQL_CONSOLE = "SELECT " +
            "COUNT(id) data1_size," +
            "COUNT(TO_DAYS(regtime)=TO_DAYS(NOW()) OR NULL) data1_day " +
            "FROM courier";
    private static final String SQL_FIND_LIMIT = "SELECT * FROM courier LIMIT ?,?";
    private static final String SQL_FIND_ALL = "SELECT * FROM courier";
    private static final String SQL_FIND_BY_USER_PHONE = "SELECT * FROM courier WHERE userphone=?";
    private static final String SQL_INSERT = "INSERT INTO courier (username,userphone,cardnumber,password,sum,regtime,lastlogintime) " +
            "VALUES(?,?,?,?,0,NOW(),NULL)";
    private static final String SQL_UPDATE = "UPDATE courier SET username=?,userphone=?,cardnumber=?,password=? WHERE id=?";
    private static final String SQL_DELETE = "DELETE FROM courier WHERE id=?";
    private static final String SQL_UPDATE_LAST_LOGIN_TIME = "UPDATE courier SET lastlogintime=NOW() WHERE userphone=?";

    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String, Integer>> data = new ArrayList<>();
        resultSet = query(SQL_CONSOLE, null);
        try {
            if (resultSet.next()) {
                int data1_size = resultSet.getInt("data1_size");
                int data1_day = resultSet.getInt("data1_day");
                Map data1 = new HashMap();
                data1.put("data1_size", data1_size);
                data1.put("data1_day", data1_day);
                data.add(data1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return data;
    }

    @Override
    public List<Courier> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<Courier> data = new ArrayList<>();
        if (limit) {
            ArrayList arrayList = new ArrayList();
            arrayList.add(offset);
            arrayList.add(pageNumber);
            resultSet = query(SQL_FIND_LIMIT, arrayList);
        } else {
            resultSet = query(SQL_FIND_ALL, null);
        }
        try {
            while (resultSet.next()) {
                Courier courier = new Courier();
                courier.setId(resultSet.getInt("id"));
                courier.setUserName(resultSet.getString("username"));
                courier.setUserPhone(resultSet.getString("userphone"));
                courier.setCardNumber(resultSet.getString("cardnumber"));
                courier.setPassword(resultSet.getString("password"));
                courier.setSum(resultSet.getInt("sum"));
                courier.setRegTime(resultSet.getTimestamp("regtime"));
                courier.setLastLoginTime(resultSet.getTimestamp("lastlogintime"));
                data.add(courier);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return data;
    }

    @Override
    public Courier findByPhone(String userPhone) {
        ArrayList arrayList = new ArrayList();
        arrayList.add(userPhone);
        resultSet = query(SQL_FIND_BY_USER_PHONE, arrayList);
        Courier courier = new Courier();
        try {
            if (resultSet.next()) {
                courier.setId(resultSet.getInt("id"));
                courier.setUserName(resultSet.getString("username"));
                courier.setUserPhone(resultSet.getString("userphone"));
                courier.setCardNumber(resultSet.getString("cardnumber"));
                courier.setPassword(resultSet.getString("password"));
                courier.setSum(resultSet.getInt("sum"));
                courier.setRegTime(resultSet.getTimestamp("regtime"));
                courier.setLastLoginTime(resultSet.getTimestamp("lastlogintime"));
            } else {
                return null;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return courier;
    }

    @Override
    public boolean insert(Courier courier) throws DuplicateUserPhoneException {
        ArrayList arrayList = new ArrayList();
        arrayList.add(courier.getUserName());
        arrayList.add(courier.getUserPhone());
        arrayList.add(courier.getCardNumber());
        arrayList.add(courier.getPassword());
        int i = 0;
        try {
            i = update(SQL_INSERT, arrayList);
        } catch (Exception e) {
            if (e.getMessage().endsWith("for key 'user.userphone'")) {
                DuplicateUserPhoneException duplicateUserPhoneException = new DuplicateUserPhoneException(e.getMessage());
                throw duplicateUserPhoneException;
            } else {
                e.printStackTrace();
            }
        } finally {
            closeAll();
        }
        if (i > 0)
            return true;
        else
            return false;
    }

    @Override
    public boolean update(int id, Courier courier) throws DuplicateUserPhoneException {
        ArrayList arrayList = new ArrayList();
        arrayList.add(courier.getUserName());
        arrayList.add(courier.getUserPhone());
        arrayList.add(courier.getCardNumber());
        arrayList.add(courier.getPassword());
        arrayList.add(id);
        int i = 0;
        try {
            i = update(SQL_UPDATE, arrayList);
        } catch (Exception throwables) {
            if (throwables.getMessage().endsWith("for key 'user.userphone'")) {
                DuplicateUserPhoneException duplicateUserPhoneException = new DuplicateUserPhoneException(throwables.getMessage());
                throw duplicateUserPhoneException;
            } else {
                throwables.printStackTrace();
            }
        } finally {
            closeAll();
        }
        if (i > 0)
            return true;
        else
            return false;
    }

    @Override
    public boolean updateLastLoginTime(String userPhone) {
        ArrayList arrayList = new ArrayList();
        arrayList.add(userPhone);
        int i = 0;
        try {
            i = update(SQL_UPDATE_LAST_LOGIN_TIME, arrayList);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        if (i > 0) {
            return true;
        } else {
            return false;
        }
    }

    @Override
    public boolean delete(int id) {
        ArrayList arrayList = new ArrayList();
        arrayList.add(id);
        int i = 0;
        try {
            i = update(SQL_DELETE, arrayList);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        if (i > 0)
            return true;
        else
            return false;
    }
}
